package com.ctrlspace.core.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ctrlspace.model.ProjectUnit;
import com.ctrlspace.util.db.Database;

public class ProjectDao {
	
	private static final String CREATE_PROJECT = "INSERT INTO projects VALUES (NULL, ?, ?, ?, ?, ?, ?)";
	private static final String GET_PROJECT_FROM_ID = "SELECT * FROM projects WHERE projectId = ?";
	private static final String GET_PROJECTS_FROM_USERID = "SELECT projectId FROM projects WHERE ownerId = ?";
	private static final String CHECK_ACCESS_GIVEN_IDS = "SELECT count(*) AS projectCount FROM projects WHERE projectId = ? AND ownerId = ?";
	
	public ProjectUnit createProject(int userId, String projectName, String description, String path, String nature) {
		Connection conn = Database.getInstance().getConnection();
		ProjectUnit projectUnit = null;

		try {
			Date date = new Date();
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			PreparedStatement st = conn.prepareStatement(CREATE_PROJECT, PreparedStatement.RETURN_GENERATED_KEYS);
			st.setInt(1, userId);
			st.setString(2, projectName);
			st.setString(3, nature);
			st.setString(4, description);
			st.setString(5, path);
			st.setString(6, formatter.format(date));
			
			int affectedRows = st.executeUpdate();
			
			// Don't attempt to get keys if there is no affected row
			if (affectedRows > 0) {
				ResultSet keys = st.getGeneratedKeys();

				// Get key, update projectuser table, return new project
				if (keys.next()) {
					int projectId = keys.getInt(1);

					projectUnit = new ProjectUnit();
					projectUnit.setProjectId(projectId);
					projectUnit.setOwnerId(userId);
					projectUnit.setTitle(projectName);
					projectUnit.setDescription(description);
					projectUnit.setPath(path);
					projectUnit.setNature(nature);
					projectUnit.setDateCreated(date);

					return projectUnit;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return projectUnit;
	}

	public ProjectUnit getProject(int projectId) {
		Connection conn = Database.getInstance().getConnection();
		ProjectUnit projectUnit = null;

		try {
			PreparedStatement st = conn.prepareStatement(GET_PROJECT_FROM_ID);
			st.setInt(1, projectId);
			
			ResultSet rs = st.executeQuery();
			
			if (rs.next()) {
				projectUnit = new ProjectUnit();
				projectUnit.setProjectId(projectId);
				projectUnit.setOwnerId(rs.getInt("ownerId"));
				projectUnit.setTitle(rs.getString("title"));
				projectUnit.setDescription(rs.getString("description"));
				projectUnit.setPath(rs.getString("path"));
				projectUnit.setNature(rs.getString("nature"));
				projectUnit.setDateCreated(rs.getDate("dateCreated"));

				return projectUnit;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return projectUnit;
	}

	public ProjectUnit openProject(int projectId) {
		Connection conn = Database.getInstance().getConnection();
		ProjectUnit projectUnit = null;

		try {
			PreparedStatement st = conn.prepareStatement(GET_PROJECT_FROM_ID);
			st.setInt(1, projectId);
			
			ResultSet rs = st.executeQuery();
			
			if (rs.next()) {
				projectUnit = new ProjectUnit();
				projectUnit.setProjectId(projectId);
				projectUnit.setOwnerId(rs.getInt("ownerId"));
				projectUnit.setTitle(rs.getString("title"));
				projectUnit.setDescription(rs.getString("description"));
				projectUnit.setPath(rs.getString("path"));
				projectUnit.setNature(rs.getString("nature"));
				projectUnit.setDateCreated(rs.getDate("dateCreated"));

				return projectUnit;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return projectUnit;
	}

	public List<Integer> getProjects(int userId) {
		Connection conn = Database.getInstance().getConnection();
		List<Integer> projectIDs = new ArrayList<Integer>();

		try {
			PreparedStatement st = conn.prepareStatement(GET_PROJECTS_FROM_USERID);
			st.setInt(1, userId);
			
			ResultSet rs = st.executeQuery();
			
			while (rs.next()) {
				projectIDs.add(rs.getInt("projectId"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return projectIDs;
	}

	public boolean checkAccess(int userId, int projectId) {
		Connection conn = Database.getInstance().getConnection();
		
		try {
			PreparedStatement st = conn.prepareStatement(CHECK_ACCESS_GIVEN_IDS);
			st.setInt(1, projectId);
			st.setInt(2, userId);
			
			ResultSet rs = st.executeQuery();
			
			if (rs.next()) {
				int count = rs.getInt("projectCount");
				
				if (count == 0) {
					return false;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return true;
	}

}
